package base;

import java.sql.*;

public class UsersBase implements UsersBaseInterface {
    private final Connection conn;

    public UsersBase() throws SQLException {
        conn = DriverManager.getConnection("jdbc:sqlite:users.db");
        sqlCreateTableFirst();
    }

    @Override
    public void close() throws SQLException {
        conn.close();
    }

    private void sqlCreateTableFirst() {
        try {
            String createSQL = """
                    CREATE TABLE IF NOT EXISTS users (
                        username VARCHAR(50) UNIQUE NOT NULL,
                        passwd   VARCHAR(100) NOT NULL,
                        balance DECIMAL(10, 2) DEFAULT 0.00)""";
            PreparedStatement pstmt = conn.prepareStatement(createSQL);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String verifyUser(String userName, String userPassword) {
        if (userName == null || userPassword == null) {
            return null;
        }
        try {
            String querySQL = "SELECT passwd FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(querySQL);
            pstmt.setString(1, userName);
            ResultSet result = pstmt.executeQuery();
            if (result.next()) {
                if (userPassword.equals(result.getString("passwd"))) {
                    return userName;
                }
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void updateUserBalance(String userName, float balance, boolean is_set_not_add) {
        try {
            if (is_set_not_add) {
                String updateSQL = "UPDATE users SET balance = ? WHERE username = ?";
                PreparedStatement pstmt = conn.prepareStatement(updateSQL);
                pstmt.setFloat(1, balance);
                pstmt.setString(2, userName);
                pstmt.executeUpdate();
            } else {
                String updateSQL = "UPDATE users SET balance = balance + ? WHERE username = ?";
                PreparedStatement pstmt = conn.prepareStatement(updateSQL);
                pstmt.setFloat(1, balance);
                pstmt.setString(2, userName);
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean isUserExist(String userName) {
        try {
            String querySQL = "SELECT username FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(querySQL);
            pstmt.setString(1, userName);
            ResultSet result = pstmt.executeQuery();
            return result.next();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 创建新用户
    @Override
    public void createUser(String userName, String userPassword) {
        try {
            String insertSQL = "INSERT INTO users (username,passwd,balance) VALUES (?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(insertSQL);
            pstmt.setString(1, userName);
            pstmt.setString(2, userPassword);
            pstmt.setInt(3, 0);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    // 查询用户余额
    @Override
    public float queryUserBalance(String userName) {
        try {
            String querySQL = "SELECT balance FROM users WHERE username = ?";
            PreparedStatement pstmt = conn.prepareStatement(querySQL);
            pstmt.setString(1, userName);
            ResultSet result = pstmt.executeQuery();
            if (result.next()) {
                return (float)result.getDouble("balance");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return -1;
    }
}
